/*
*James Bienz(JB)
*Library CRUD Exercises in the Database.
*
*GNU GPL v2
*/

package com.ist412.pocke.SQL;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.ist412.pocke.DTO.ExerciseDTO;

public class Exercise {
	
	//TableName
	private static final String TABLE_NAME= "Exercise";
	private static final String ASSOCIATIVE_TABLE = "Workout_has_Exercise";
	
	//Associative Table Indices
	private static final int WORKOUT_ID_INDEX = 0;
	private static final int EXERCISE_ID_INDEX = 1;
	
	//Associative Table Keys
	private static final String WORKOUT_ID_KEY = "WORKOUT_ID";
	private static final String EXERCISE_ID_KEY = "EXERCISE_ID";
	
	//Exercise Indices
	private static final int ID_INDEX = 0;
	private static final int EXERCISE_NAME_INDEX = 1;
	private static final int MUSCLE_GROUP_ID_INDEX = 2;
	
	//Exercise Keys
	private static final String EXERCISE_NAME_KEY = "exerciseName";
	private static final String MUSCLE_GROUP_KEY = "MUSCLEGROUP_ID";
	
	public static ArrayList<ExerciseDTO> getExercises(Context newContext){
		SQLMachine sql = new SQLMachine(newContext);
		SQLiteDatabase db = sql.getReadableDatabase();
		ArrayList<ExerciseDTO> exercises = new ArrayList<ExerciseDTO>();
		String query = "SELECT * FROM Exercise";
		Cursor cursor = db.rawQuery(query, new String[] {});
		while(cursor.moveToNext()){
			ExerciseDTO exercise = new ExerciseDTO();
			exercise.setID(cursor.getInt(ID_INDEX));
			exercise.setExerciseName(cursor.getString(EXERCISE_NAME_INDEX));
			exercise.setMuscleGroupID(cursor.getInt(MUSCLE_GROUP_ID_INDEX));
			exercises.add(exercise);
		}
		return exercises;
	}
	
	public static ArrayList<ExerciseDTO> getExercises(Context newContext, int muscleGroupID){
		SQLMachine sql = new SQLMachine(newContext);
		SQLiteDatabase db = sql.getReadableDatabase();
		ArrayList<ExerciseDTO> exercises = new ArrayList<ExerciseDTO>();
		String query = "SELECT * FROM Exercise WHERE muscleGroupID=?";
		Cursor cursor = db.rawQuery(query, new String[] {String.valueOf(muscleGroupID)});
		while(cursor.moveToNext()){
			ExerciseDTO exercise = new ExerciseDTO();
			exercise.setID(cursor.getInt(ID_INDEX));
			exercise.setExerciseName(cursor.getString(EXERCISE_NAME_INDEX));
			exercise.setMuscleGroupID(cursor.getInt(MUSCLE_GROUP_ID_INDEX));
			exercises.add(exercise);
		}
		return exercises;
	}
	
	public static ArrayList<ExerciseDTO> getExercisesByWorkout(Context newContext, int workoutId){
		SQLMachine sql = new SQLMachine(newContext);
		SQLiteDatabase db = sql.getReadableDatabase();
		String query = "SELECT * FROM " + ASSOCIATIVE_TABLE + " WHERE WORKOUT_ID=?";
		Cursor cur = db.rawQuery(query, new String[] {String.valueOf(workoutId)});
		ArrayList<ExerciseDTO> exercises = new ArrayList<ExerciseDTO>();
		while(cur.moveToNext()){
			String exerciseQuery = "SELECT * FROM " + TABLE_NAME + " WHERE id=?";
			Cursor exerciseCursor = db.rawQuery(exerciseQuery, new String[] {String.valueOf(cur.getInt(EXERCISE_ID_INDEX))});
			if(exerciseCursor.moveToFirst()){
				ExerciseDTO exercise = new ExerciseDTO();
				exercise.setID(exerciseCursor.getInt(ID_INDEX));
				exercise.setExerciseName(exerciseCursor.getString(EXERCISE_NAME_INDEX));
				exercise.setMuscleGroupID(exerciseCursor.getInt(MUSCLE_GROUP_ID_INDEX));
				exercises.add(exercise);
			}
		}
		return exercises;
	}
	
	public static ExerciseDTO getExercise(Context newContext, int id){
		SQLMachine sql = new SQLMachine(newContext);
		SQLiteDatabase db = sql.getReadableDatabase();
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE id=?";
		Cursor cursor = db.rawQuery(query, new String[] {String.valueOf(id)});
		ExerciseDTO exercise = new ExerciseDTO();
		if(cursor.moveToFirst()){
			exercise.setID(cursor.getInt(ID_INDEX));
			exercise.setExerciseName(cursor.getString(EXERCISE_NAME_INDEX));
			exercise.setMuscleGroupID(cursor.getInt(MUSCLE_GROUP_ID_INDEX));
		}
		return exercise;
	}
	
	public static long createExercise(Context newContext, ExerciseDTO exercise){
		SQLMachine sql = new SQLMachine(newContext);
		SQLiteDatabase db = sql.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(EXERCISE_NAME_KEY, exercise.getExerciseName());
		values.put(MUSCLE_GROUP_KEY, exercise.getMuscleGroupID());
		return db.insert(TABLE_NAME, null, values);
	}
	
	public static long addWorkoutAssociation(Context newContext,int exerciseId, int workoutId){
		SQLMachine sql = new SQLMachine(newContext);
		SQLiteDatabase db = sql.getWritableDatabase();
		ContentValues aValues = new ContentValues();
		aValues.put(WORKOUT_ID_KEY, workoutId);
		aValues.put(EXERCISE_ID_KEY, exerciseId);
		return db.insert(ASSOCIATIVE_TABLE, null, aValues);
	}
	
	public static long createExercise(Context newContext, ExerciseDTO exercise, int workoutID){
		SQLMachine sql = new SQLMachine(newContext);
		SQLiteDatabase db = sql.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(EXERCISE_NAME_KEY, exercise.getExerciseName());
		values.put(MUSCLE_GROUP_KEY, exercise.getMuscleGroupID());
		long exerciseID = db.insert(TABLE_NAME, null, values);
		ContentValues aValues = new ContentValues();
		aValues.put(WORKOUT_ID_KEY, workoutID);
		aValues.put(EXERCISE_ID_KEY, exerciseID);
		db.insert(ASSOCIATIVE_TABLE, null, aValues);
		return exerciseID;
	}
	
	public static int updateExercise(Context newContext, ExerciseDTO exercise){
		SQLMachine sql = new SQLMachine(newContext);
		SQLiteDatabase db = sql.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(EXERCISE_NAME_KEY, exercise.getExerciseName());
		values.put(MUSCLE_GROUP_KEY, exercise.getMuscleGroupID());
		String filter = "id=" + exercise.getID();
		return db.update(TABLE_NAME, values, filter, null);
	}
	
	public static int deleteExercise(Context newContext, int id){
		SQLMachine sql = new SQLMachine(newContext);
		SQLiteDatabase db = sql.getWritableDatabase();
		String whereClause = "id=" + id;
		return db.delete(TABLE_NAME, whereClause, null);
	}
}
